-- @owner: @zou_jialiang050
-- @date: 2022/02/24
-- @testpoint: 在插入数据时触发更新及查看触发器

--step1:建表及插入数据;expect:成功
drop table if exists t_clob cascade;
create table t_clob(c_id int,b_clob clob) with (storage_type=ustore);
insert into t_clob values(1,'35466');
insert into t_clob values(2,'3546213457869708234444479806');
insert into t_clob values(3,'3546213457864444479806');
insert into t_clob values(4,'35463457686543122453536');
insert into t_clob values(5,'354621345784533669708234444479806');
insert into t_clob values(6,'354621345743567378567864444479806');
insert into t_clob values(7,'100000');
insert into t_clob values(8,'101000');
insert into t_clob values(9,'1000100');
insert into t_clob values(10,'c4711a1097876cc');
insert into t_clob values(11,'');
insert into t_clob values(12,null);
--step2:在插入数据时触发更新及查看触发器;expect:成功
create or replace function tri_update_func() returns trigger as
$$
declare
declare  cc_clob clob;
begin
 update t_clob set b_clob= b_clob || '35466' where c_id=1;
 select b_clob into cc_clob from t_clob where c_id=1;
 raise info '%',('result:'||cc_clob);
return new;
end;
$$ language plpgsql;
/
create  trigger t_clob_t before insert on t_clob
for each row
execute procedure tri_update_func();
/
insert into t_clob values(1,'35466');
select * from t_clob where c_id=1;
--step3:清理环境;expect:成功
drop trigger t_clob_t on t_clob cascade;
drop table  t_clob cascade;